package model;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by xiebang
 * Description
 * User:小谢
 * Date:2022-03-10
 * Time:16:51
 */
//针对blog 表进行增删改查的操作
//  暂时不考虑博客修改
//    一下代码为存粹的jdbc 代码
public class BlogDao {
    //1.新增一个博客 ==》 博客编辑页

    public void insert(Blog blog) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            //1.于数据库建立连接
            connection = DBUtil.getConnection();

//            2.拼装sql语句
//            对于自增主键来说，不需要手动插入，直接写作null 就会由mysql 自动编排
            String sql = "insert into blog values(null,?,?,?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1, blog.getTitle());
            statement.setString(2, blog.getContent());
            statement.setTimestamp(3, blog.getPostTime());
            statement.setInt(4, blog.getUserId());

//            执行语句
            int ret = statement.executeUpdate();
            if (ret != 1) {
                System.out.println("插入失败");
            } else {
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //4.释放必要的资源
            DBUtil.close(connection, statement, null);
        }
    }
    //2.查找博客列表 ==》 博客列表页
// 此处咱们是简单出包的把数据库所有的博客数据都给查询出来，但是这种操作并不科学，有的时候
//    数据库中的内容可能回特别多 导致查询操作回非常卡,也可能导致返回给前端的数据特别多
//    更常见的操作,是分页查询.(limit offset)
    public List<Blog> selectAll(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Blog> blogs = new ArrayList<>();

        try {
            //1.建立连接
            connection = DBUtil.getConnection();
//            2.构建sql语句
            String sql = "select * from blog order by postTime desc";
            statement = connection.prepareStatement(sql);
//          3.执行sql
            resultSet =  statement.executeQuery();
            while (resultSet.next()){
                  Blog blog = new Blog();
                  blog.setBlogId(resultSet.getInt("blogId"));
                  blog.setTitle(resultSet.getString("title"));
                  String content =resultSet.getString("content");
                  if (content.length() > 100){
                      content = content.substring(0,100) + "...";
                  }
                  blog.setContent(content);
                  blog.setPostTime(resultSet.getTimestamp("postTime"));
                  blog.setUserId(resultSet.getInt("userId"));
                  blogs.add(blog);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return  blogs;
    }
    //3.查找指定博客的详情  == 》 博客详情页
    public Blog select(int BlogId) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Blog blog = new Blog();


        try {
            //        1.建立连接
            connection = DBUtil.getConnection();
            //        2.建立sql语句
            String sql = "select * from blog where blogId = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,BlogId);
           resultSet = statement.executeQuery();
           if (resultSet.next()){

               blog.setBlogId(resultSet.getInt("blogId"));
               blog.setPostTime(resultSet.getTimestamp("postTime"));
               blog.setTitle(resultSet.getString("title"));
               blog.setContent(resultSet.getString("content"));
               blog.setUserId(resultSet.getInt("userId"));
               return blog;
           }
//           查询不到结果,直接返回null
           return null;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }
    //        这个return对应抛出异常的情况,查询过程抛异常,也相当于查询失败,返回null
        return null;
    }
    //4.删除一个博客 ==》 博客详情页上加一个删除按钮
    public void deleteBlog(int blogId){
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "delete from blog where blogId = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,blogId);
            int ret = statement.executeUpdate();
            if (ret != 1){
                System.out.println("删除失败");
            }else {
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,null);
        }
    }
//    通过这个代码，对于数据库进行单独的测试
    public static void main(String[] args) {
        BlogDao blogDao = new BlogDao();
        //1.测试插入
        Blog blog = new Blog();
        blog.setTitle("我的第3篇blog");
        blog.setContent("我是正文3");
        blog.setPostTime(new Timestamp(System.currentTimeMillis()));
        blog.setUserId(1);
        blogDao.insert(blog);

    //    // 测试查询
    //    List<Blog> blogs = blogDao.selectAll();
    //    System.out.println(blogs);

//    //    测试查询id
//        Blog blog = new Blog();
//        blog= blogDao.select(1);
//        System.out.println(blog);

        //测试删除
//        blogDao.deleteBlog(1);
    }



}